#!/usr/bin/python3
# coding-utf-8

import pandas as pd
from datetime import datetime, timedelta
import random

# dataframe = worksheet
# dataset = columns


def add_month(current_date, exp_months):
    year_gap = exp_months // 12
    new_month = current_date.month + exp_months % 12
    if new_month != 12:
        year_gap += new_month // 12
        new_month = new_month % 12

    return datetime(current_date.year + year_gap, new_month, current_date.day)


def get_upload_record(
    env,
    tenant_id,
    tax_num,
    business_type,
    channel,
    is_platform_upload,
    excel_file,
    sheet_name="usage",
):
    is_found, record = False, None
    df = pd.read_excel(excel_file, sheet_name=sheet_name, dtype=str)
    df.fillna("", inplace=True)
    # Filter env
    df = df.loc[df.env.apply(lambda act: True if act == env else False)]
    # print(df)
    # Filter tenant_id
    df = df.loc[
        df.tenant_id.apply(lambda act: True if str(act) == str(tenant_id) else False)
    ]
    # print(df)
    # Filter tax_num
    df = df.loc[df.tax_num.apply(lambda act: True if act == tax_num else False)]
    # print(df)
    # Filter business_type
    if business_type.lower():
        df = df.loc[
            df.business_type.apply(
                lambda act: True if act.lower() == business_type.lower() else False
            )
        ]
    # print(df)
    # Filter channel
    if channel.lower():
        df = df.loc[
            df.channel.apply(
                lambda act: True if act.lower() == channel.lower() else False
            )
        ]
    # print(df)
    # Filter source_type
    if is_platform_upload:
        df = df.loc[
            df.source_code.apply(
                lambda act: True if act.lower() == "INTEGRATION".lower() else False
            )
        ]
    if df.size.size == 1:
        is_found = True
        for item in df.itertuples():
            record = item
    elif df.size.size == 0:
        print("没找到有效数据，请手工确认")
    else:
        print("数据异常:\n", df)

    return is_found, record


def get_Column_Value():
    """ """
    file = "./bill_engine_rules.xlsx"
    column = "tax_start_date"
    dataset = pd.read_excel(file, sheet_name="contracts_tmp")
    dataset = dataset.loc[dataset["trigger_step"] == 1]
    dataset = dataset.drop_duplicates("tax_start_date")
    print(str(dataset["tax_start_date"].max())[:10])


if __name__ == "__main__":
    # env = 'FAT'
    # file = './Job_Prices.xlsx'
    # tenant_id = '5692152094097178624'
    # tax_num = '92100000JGFR16W0X5'
    # business_type = 'Verify'
    # channel = 'Account'
    # is_platform_upload = False
    # get_upload_record(env, tenant_id, tax_num, business_type, channel, is_platform_upload, file)
    get_Column_Value()

# # 第一节
# # 1. 利用pandas 创建Excel文件
# # 1. 利用dataframe 直接创建Excel
# df = DataFrame({'ID': [1, 2, 3], 'Name': ['Tom', 'Jerry', '张三']})
# print(df)
# df.to_excel('./aaa.xlsx')

# # 2. 建立索引,如果没指定， 则会自动创建一列
# df = df.set_index('ID')
# print(df)
# df.to_excel('./aaa.xlsx')

# # 第二节
# # 1. 读文件
# people = pd.read_excel('./people.xlsx')
# # 读取Excel的行列数
# print(people.shape)
# # 读取Excel的列名
# print(people.columns)
# # 读取Excel的前后行数， 默认是5行
# print(people.head)
# print(people.tail)
# print(people.head(3))
# # 读取Excel从指定的位置开始， 默认行数的下标是从0开始
# people1 = pd.read_excel('./people1.xlsx', header=1)
# print(people1.columns)
# # 读取Excel从指定的位置开始， 不指定列名时，header=None
# people1 = pd.read_excel('./people1.xlsx', header=None)
# print(people1.head)

# # 建立索引的第二种方法
# people = pd.read_excel('./people.xlsx')
# people.set_index('ID',inplace=True)
# people.to_excel('./people.xlsx')

# # 第三节， 行、列的操作 series
# d1 = {'x': 100, 'y': 200, 'z': 300}
# s1 = pd.Series(d1)
# print(s1)

# s2 = pd.Series([10, 20, 30], index=[1, 2, 3])
# print(s2)

# # index != d1.keys时， 默认返回NaN(not a number)
# s9 = pd.Series(data=d1, index = ['a','b','c'])
# print(s9)

# s3 = pd.Series([10, 20, 30], index=[1, 2, 3], name='A1')
# s4 = pd.Series([100, 200, 300], index=[1, 2, 3], name='B1')
# s5 = pd.Series([1000, 2000, 3000], index=[1, 2, 3], name='C1')
# df = pd.DataFrame({s3.name: s3, s4.name: s4, s5.name: s5})
# print(df)

# df1 = pd.DataFrame([s3,s4,s5])
# print(df1)

# # index不一致时， 并集显示, 没有时，默认用NaN显示
# s6 = pd.Series([10, 20, 30], index=[1, 2, 3], name='A1')
# s7 = pd.Series([100, 200, 300], index=[2, 3, 4], name='B1')
# s8 = pd.Series([1000, 2000, 3000], index=[1, 3, 5], name='C1')
# df2 = pd.DataFrame({s6.name: s6, s7.name: s7, s8.name: s8})
# df2.to_excel('./bbb.xlsx')
# print(df2)

# # 第四节, 自动填充
# # 跳过前几行关键字=skiprows，去掉前几列=usecols, 指定列的类型=dtype
# books = pd.read_excel('./Books.xlsx',
#                       skiprows=5,
#                       usecols='C:H',
#                       dtype={
#                           'ID': str,
#                           'Price': str,
#                           'Date': str
#                       })
# # print(books)
# # print(books.index)
# print(books.columns)
# start = datetime(2021,1,12)
# for i in books.index:
#     # # 通过获取books.series再去修改数据
#     # books['ID'].at[i] = i + 1
#     # books['Price'].at[i] = int(100 * random.random())
#     # books['Discount'].at[i] = round(random.random(),2)
#     # books['Date'].at[i] = add_month(start, i)
#     # 直接针对单元格进行数据修正
#     books.at[i,'ID'] = i * 1
#     books.at[i,'Price'] = int(100 * random.random())
#     books.at[i,'Discount']  = round(random.random(),2)
#     books.at[i,'Date']  = add_month(start, i)

# print(books)
# books.set_index('ID', inplace=True)
# books.to_excel('./result.xlsx')

# books = pd.read_excel('./result.xlsx', index_col='ID')
# # 直接操作列series
# books['SalePrice'] = books['Price'] * (1 - books['Discount'])
# # 也可以操作单元格
# for i in books.index:
#     books.at[i,'SalePrice'] = books.at[i,'Price'] * (1 - books.at[i,'Discount'])

# # Price 价格需要增加10元
# # 方法一
# books['Price'] +=2
# # 方法二 Series.apply + lambda
# books['Price'] = books['Price'].apply(lambda x:x+5)
# print(books)

# # 第7节，排序 sort_values, ascending=True(小到大)
# books = pd.read_excel('./result.xlsx')
# # 单列, Price 从大到小排序
# books.sort_values(by='Price', inplace=True, ascending=False)
# # 多列，Price 从小到大， discount 从大到小
# books.sort_values(by=['Price','Discount'],inplace=True,ascending=[True, False])
# print(books)

# # 第8节，过滤数据 loc(定位)
# books = pd.read_excel('./result.xlsx')
# # 过滤Price在10~50之间的记录
# books = books.loc[books.Price.apply(lambda p: 10<p<50)]
# # 过滤Price在10~50， Discount在0.5~0.8 之间的记录
# books = books.loc[books.Price.apply(lambda p: 10<p<50)].loc[books.Discount.apply(lambda d: 0.5<d<0.8)]
# print(books)

# # 第9节，画柱状图
# books = pd.read_excel('./result.xlsx', index_col='ID')
# books.sort_values(by=['Price', 'Discount'],
#                   ascending=[False, True],
#                   inplace=True)
# # print(books)
# # books的制图
# # books.plot.bar(x='Discount',y='Price', color='orange', title='Demo BarChart')
# # matplotlib 制图
# plt.bar(books.Discount, books.Price, color='orange')
# # plt.xticks(books.Discount, rotation='90')
# plt.xlabel('Discount')
# plt.ylabel('Price')
# plt.title('Use matplotlib to draw bar', fontsize=20)
# plt.tight_layout()
# plt.show()

# # 第10节， 画柱状图比较
# students = pd.read_excel('./Students.xlsx')
# students.sort_values(by='2016',inplace=True, ascending=False)
# # print(students)
# students.plot.bar(x='Field',y=['2016','2017'],color=['black','yellow'])
# plt.title('International Student Trendency', fontsize=18, fontweight='bold')
# plt.xlabel('Field',fontsize=14)
# plt.ylabel('Number',fontweight='bold')
# ax = plt.gca()
# ax.set_xticklabels(students.Field, rotation=45, ha='right')
# f = plt.gcf()
# f.subplots_adjust(left=0.2, bottom=0.5)
# # plt.tight_layout()
# plt.show()

# # 自学列统计
# file = './price_tenant_rules.xlsx'
# usage_tenants = pd.read_excel(file, sheet_name='usage1')
# # group_tenant = scenarios.groupby(scenarios['tenant_id'])
# # # print(group_tenant.sum
# # for tenant_id, item in group_tenant:
# #     print(tenant_id)
# #     print(item['tenant_id'].mean())
# #     print(item['tenant_code'])
# #     print(item['exp_upload_count'].sum())
# target_tenant_ids = []
# usage_tenants = usage_tenants.groupby(
#     ['env', 'tenant_id', 'tenant_code', 'is_upload'])
# for key, item in usage_tenants:
#     env, tenant_id, tenant_code, is_upload = key
#     exp_upload_count = item['exp_upload_count'].sum()
#     target_tenant_ids.append(
#         [env, tenant_id, tenant_code, is_upload, exp_upload_count])
# print(target_tenant_ids)

# # 数据过滤/筛选
# def need_upload_records(is_upload):
#     # print(is_upload)
#     return True if is_upload == 'Yes' else False

# def need_env_records(is_upload):
#     # print(is_upload)
#     return True if is_upload == 'FAT' else False

# file = './price_tenant_rules.xlsx'
# usage_tenants = pd.read_excel(file, sheet_name='usage', dtype=str)
# # usage_tenants = usage_tenants.loc[usage_tenants['is_upload'].apply(need_upload_records)].loc[usage_tenants['env'].apply(need_env_records)]
# usage_tenants = usage_tenants.loc[usage_tenants.is_upload.apply(need_upload_records)].loc[usage_tenants.env.apply(need_env_records)]
# print(usage_tenants)
